namespace CISLib
{
    using System;
    using System.Data;
    using System.Data.SqlClient;

    public class ManageAnnounce
    {
        public static SqlDataReader GetAnnounces()
        {
            string strSQL = "Select  *  from TB_Announce where IsPublished = '1' order by IssueDateTime desc";
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            return MyDB.DBReader;
        }

        public static DataSet GetAnnounces(Guid areaId)
        {
            string strSQL = "Select  *  from TB_Announce where IsPublished = '1' and AreaId=@AreaId order by IssueDateTime desc";
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(areaId, "AreaId", SqlDbType.UniqueIdentifier);
            MyDB.DBSetSelect(strSQL);
            return MyDB.DBReturnDataSet();
        }

        public static SqlDataReader GetAnnouncesInAdmin()
        {
            string strSQL = "Select  *  from TB_Announce  order by IssueDateTime desc";
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            return MyDB.DBReader;
        }

        public static DataSet GetAnnouncesInAdmin(Guid areaId)
        {
            string strSQL = "Select  *  from TB_Announce where AreaId=@AreaId order by IssueDateTime desc";
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(areaId, "AreaId", SqlDbType.UniqueIdentifier);
            MyDB.DBSetSelect(strSQL);
            return MyDB.DBReturnDataSet();
        }

        public static Announce GetAnnounceViaId(Guid id)
        {
            SqlDataReader MyReader = null;
            Announce announce = new Announce();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            string strSQL = "select * from TB_Announce where AnnounceId = @AnnounceId";
            MyDB.AddParameters(id, "AnnounceId", SqlDbType.UniqueIdentifier);
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            MyReader = MyDB.DBReader;
            if (MyReader.Read())
            {
                announce.AnnounceId = MyReader.GetGuid(0);
                announce.Title = MyReader.GetString(1);
                announce.Content = MyReader.GetString(2);
                announce.IssueDateTime = MyReader.GetDateTime(3);
                announce.IsPublished = Convert.ToBoolean(MyReader.GetInt32(4));
            }
            MyDB.DBClose();
            return announce;
        }

        public static void InsertNewAnnounce(string title, string context, bool isPublished, Guid areaId)
        {
            Guid id = Guid.NewGuid();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(title, "Title", SqlDbType.VarChar);
            MyDB.AddParameters(id, "AnnounceId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(areaId, "AreaId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(context, "Content", SqlDbType.VarChar);
            int published = isPublished ? 1 : 0;
            MyDB.AddParameters(published, "IsPublished", SqlDbType.Int);
            DateTime nowTime = DateTime.Now;
            MyDB.AddParameters(nowTime, "IssueDateTime", SqlDbType.DateTime);
            string strSQL = "Insert into TB_Announce (AnnounceId,Title,Content,IsPublished,IssueDateTime,AreaId) values (@AnnounceId,@Title,@Content,@IsPublished,@IssueDateTime,@AreaId)";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        public static void RemoveAnnounceViaId(Guid id)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(id, "AnnounceId", SqlDbType.UniqueIdentifier);
            string strSQL = "Delete From TB_Announce where AnnounceId = @AnnounceId";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        public static void UpdateAnnounce(Guid id, string title, string context, bool isPublished)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(id, "AnnounceId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(title, "Title", SqlDbType.VarChar);
            MyDB.AddParameters(context, "Content", SqlDbType.VarChar);
            int published = isPublished ? 1 : 0;
            MyDB.AddParameters(published, "IsPublished", SqlDbType.Int);
            DateTime nowTime = DateTime.Now;
            MyDB.AddParameters(nowTime, "IssueDateTime", SqlDbType.DateTime);
            string strSQL = "Update TB_Announce set Title = @Title,Content = @Content,IsPublished = @IsPublished,IssueDateTime = @IssueDateTime where AnnounceId = @AnnounceId";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }
    }
}

